package JDBC;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import SwingModel.CmsAssignment;
import SwingModel.CmsAssignmentSubmission;

public class StudentAssignmentActivities {
	
	//To view list of Available Assignments
	public static List<CmsAssignment> getListOfAssignments(String username, String courseID){
		List<CmsAssignment> listOfAssignments = new ArrayList();
		
		 try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			System.out.println("Retrieve assignment for this courseID - " + courseID);
			pstmt = dCon.connection.prepareStatement("select * from dbcms.Assignments where courseID=? and AssigAvailDTM <= CURRENT_TIMESTAMP and AssigExpDTM > CURRENT_TIMESTAMP");
			pstmt.setString(1,courseID);
			ResultSet resultSet = pstmt.executeQuery();
			
			while (resultSet.next()){
				System.out.println("StudentAssignmentActivities.getListOfAssignment -Inside ResultSet");					
				int asgmId= resultSet.getInt("AssigId");					
				System.out.println("Retrieve this asgmID - " + asgmId);
				pstmt = dCon.connection.prepareStatement("select * from dbcms.Assig_Files where AssigId=?");
				pstmt.setInt(1, asgmId);
				ResultSet fileResultSet = pstmt.executeQuery(); //GAYI: you need a different result set to store the Assig_Files data
				
				List<File> asgmFile = new ArrayList(); 
				
				while(fileResultSet.next()){
					System.out.println("File path - " + fileResultSet.getString("AssigFilePath"));
					asgmFile.add(new File(fileResultSet.getString("AssigFilePath")));
				}
				
				//add assignment to the list 
				CmsAssignment asgm =new CmsAssignment(asgmId, resultSet.getString("AssigTitle"), resultSet.getString("AssigDescr"), asgmFile, 
						resultSet.getDouble("PercWeight"),resultSet.getTimestamp("CreatedDTM"), resultSet.getTimestamp("AssigAvailDTM"), resultSet.getTimestamp("AssigDueDTM"), 
						resultSet.getTimestamp("AssigExpDTM"));
				
				System.out.println("RETRIEVED ASSIGNMENT:\n" + asgm.toString() + "\n-----------------------\n\n");
				listOfAssignments.add(asgm);
				
			}
			dCon.close();
		} catch(Exception e){}		
		
		 
		return listOfAssignments;
	}

	
	public static void submitAssignment(CmsAssignmentSubmission asgmSub){
		try{
			System.out.println("Inside submit assignment");
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			
			//insert data into User_Assig first
			pstmt = dCon.connection.prepareStatement(
					"update dbcms.User_Assig SET submittedDTM = ?, Grade = ?, isPublisher = ? WHERE assigId = ? AND username = ? ;");
			pstmt.setInt(3,asgmSub.getAsgmID());
			pstmt.setString(4, asgmSub.getStudentId());
			pstmt.setInt(5, 0);
			SimpleDateFormat sdf = new SimpleDateFormat("YYYY:MM:dd HH:mm:ss");			
			pstmt.setString(1, sdf.format(asgmSub.getCreatedDate()));
			pstmt.setDouble(2,asgmSub.getGivenPoint());
			pstmt.execute();
			
			
			//Iterate through the list of file to upload one file at a time
			for(File file : asgmSub.getSubFiles()){
				pstmt = dCon.connection.prepareStatement(
						"insert into dbcms.Assig_Submission (AssigId, username, AssigSubFilepath, CreatedDTM)"
						+ " values ( ?, ?, ?, ?);");
				//get assignment id from asgm submission
				pstmt.setInt(1,asgmSub.getAsgmID());
	
				//get student id from asgm submission
				pstmt.setString(2,asgmSub.getStudentId());
				
				//get file path
				pstmt.setString(3, file.getPath());
				
				//created Date		
				pstmt.setString(4, sdf.format(asgmSub.getCreatedDate()));
				
				pstmt.execute();			
			}
			dCon.close();				
		} catch(Exception e){
			e.printStackTrace();
			
		}
	}
		
	public static void reSubmitAssignment(CmsAssignmentSubmission asgmSub){
		try{
		
			//Delete last submission
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			
			//insert data into User_Assig first
			PreparedStatement pstmt = dCon.connection.prepareStatement(
					"update dbcms.User_Assig SET submittedDTM = ?, Grade = ?, isPublisher = ? WHERE assigId = ? AND username = ? ;");
			pstmt.setInt(3,asgmSub.getAsgmID());
			pstmt.setString(4, asgmSub.getStudentId());
			pstmt.setInt(5, 0);
			SimpleDateFormat sdf = new SimpleDateFormat("YYYY:MM:dd HH:mm:ss");			
			pstmt.setString(1, sdf.format(asgmSub.getCreatedDate()));
			pstmt.setDouble(2,asgmSub.getGivenPoint());
			pstmt.execute();
			
			pstmt = dCon.connection.prepareStatement(
					"delete from dbcms.Assig_Submission where assigID = ? and username = ?;");
			pstmt.setInt(1, asgmSub.getAsgmID());
			pstmt.setString(2, asgmSub.getStudentId());
			pstmt.execute();//done deleting
			
			
			//add new submission
			//Iterate through the list of file to upload one file at a time
			for(File file : asgmSub.getSubFiles()){
				pstmt = dCon.connection.prepareStatement(
						"insert into dbcms.Assig_Submission (AssigId, username, AssigSubFilepath, CreatedDTM)"
								+ " values ( ?, ?, ?, ?);");
				//get assignment id from asgm submission
				pstmt.setInt(1,asgmSub.getAsgmID());
	
				//get student id from asgm submission
				pstmt.setString(2,asgmSub.getStudentId());
				
				//get file path
				pstmt.setString(3, file.getPath());
				
				//created Date
				sdf = new SimpleDateFormat("YYYY:MM:dd HH:mm:ss");			
				pstmt.setString(4, sdf.format(asgmSub.getCreatedDate()));
				
				pstmt.execute();			
			}
			dCon.close();				
		} catch(Exception e){
			e.printStackTrace();
			
		}
	}
	
	public static CmsAssignmentSubmission getSubmission(String username, int asgmID){
		CmsAssignmentSubmission asgmSub = null;
		try{
			
			//Retrieve submission
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.connection.prepareStatement(
					"select * from dbcms.Assig_Submission WHERE username = ? AND assigID = ?");
			pstmt.setString(1, username);
			pstmt.setInt(2, asgmID);
			ResultSet resultSet = pstmt.executeQuery();
			
			pstmt = dCon.connection.prepareStatement(
					"select * from dbcms.User_Assig WHERE username = ? AND assigID = ?");
			pstmt.setString(1, username);
			pstmt.setInt(2, asgmID);
			ResultSet gradeRS = pstmt.executeQuery();
			double givenPoint = 0.0;
			Date createdDate = new Date();
			if(gradeRS.next()){
				givenPoint = gradeRS.getDouble("Grade");
			}
			
			List<File> subFiles = new ArrayList();
			while(resultSet.next()){
				createdDate = new Date(resultSet.getTimestamp("CreatedDTM").getTime());
				subFiles.add(new File(resultSet.getString("AssigSubFilePath")));
			}
			
			asgmSub = new CmsAssignmentSubmission(0, username, asgmID, subFiles, createdDate, givenPoint);
			
			dCon.close();				
		} catch(Exception e){
			e.printStackTrace();
		}
		
		return asgmSub;
	}

}
